package com.systemsjr.jrbbf.expense;

import java.util.Date;
import java.util.Vector;

import net.rim.device.api.database.Cursor;
import net.rim.device.api.database.DataTypeException;
import net.rim.device.api.database.Database;
import net.rim.device.api.database.DatabaseException;
import net.rim.device.api.database.Row;
import net.rim.device.api.database.Statement;

import com.systemsjr.jrbbf.ExpenseUI;
import com.systemsjr.jrbbf.account.Account;
import com.systemsjr.jrbbf.account.ManageAccount;
import com.systemsjr.jrbbf.common.FinanceEntity;
import com.systemsjr.jrbbf.db.SQLManager;

public class ManageExpense extends SQLManager {

	public ManageExpense(Database db) {
		super(db);
	}
	
	/**
     * Adds a new category to the Category database table
     * @param name The name of the Category to be added
     * @return A new Category object
     */
    public Expense add(Expense entity)
    {        
        try
        {
            
            Statement statement = _db.createStatement("INSERT INTO Expense VALUES(null, ?, ?, ?, ?, ?)");                     
            statement.prepare();
            statement.bind(1, entity.getExpenseType().getId());
            statement.bind(2, entity.getExpenseAmount());
            statement.bind(3, entity.getAccount().getId());
            statement.bind(4, entity.getTransType());
            statement.bind(5, entity.getDate().getTime());
            statement.execute(); 
            statement.close(); 
            
            // Query the database for the auto-generated ID of the expense just added
            // and create a new Category object.
            statement = _db.createStatement("SELECT id FROM Expense WHERE expenseType = ? and amount = ? and account=? and transType=? and date=?"); 
            statement.prepare();
            statement.bind(1, entity.getExpenseType().getId());
            statement.bind(2, entity.getExpenseAmount());
            statement.bind(3, entity.getAccount().getId());
            statement.bind(4, entity.getTransType());
            statement.bind(5, entity.getDate().getTime());
            Cursor cursor = statement.getCursor();                        
            if(cursor.next())
            {
                Row row = cursor.getRow();
                int id = row.getInteger(0);
                entity.setId(id);                            
            }                 
            cursor.close();
            statement.close();
        }
        catch(DatabaseException dbe)
        {
            ExpenseUI.errorDialog(dbe.toString());
        }    
        catch(DataTypeException dte)
        {
        	ExpenseUI.errorDialog(dte.toString());
        }
        
        return entity;
    }
    

    /**
     * Updates an existing record in the DirectoryItems table
     * @param id The id of the item to update
     * @param name The text with which to update the name field
     * @param location The text with which to update the location field
     * @param phone The text with which to update the phone field
     */
    public void update(Expense entity)
    {
        try
        {          
            // Update the record in the DirectoryItems table for the given id
            Statement statement = _db.createStatement("UPDATE Expense SET fkExpenseType = ?, amount = ?, fkAccount = ?, transType = ?, date=? WHERE id = ?"); 
            statement.prepare();                
            statement.bind(1, entity.getExpenseType().getId());
            statement.bind(2, entity.getExpenseAmount());
            statement.bind(3, entity.getAccount().getId());       
            statement.bind(4, entity.getTransType());
            statement.bind(5, entity.getDate().getTime());
            statement.bind(6, entity.getId());                      
            statement.execute();                                           
            statement.close();
        }
        catch(DatabaseException dbe)
        {
        	ExpenseUI.errorDialog(dbe.toString());   
        }                
    }
    

    /**
     * Deletes a category from the Category table and all corresponding records
     * in the DirectoryItems table.
     * @param id The id of the category to delete
     */
    public void delete(FinanceEntity entity)
    {
        try
        {
            // Delete all items in the DirectoryItems database
            // table belonging to the highlighted category.            
            Statement statement = _db.createStatement("DELETE FROM Expense WHERE id = ?"); 
            statement.prepare();
            statement.bind(1, entity.getId());            
            statement.execute();  
            statement.close();
        
        }
        catch(DatabaseException dbe)
        {
        	ExpenseUI.errorDialog(dbe.toString());
        }        
    }    
    
    /**
     * Deletes a category from the Category table and all corresponding records
     * in the DirectoryItems table.
     * @param id The id of the category to delete
     */
    public void deleteByType(FinanceEntity entity)
    {
        try
        {
            // Delete all items in the DirectoryItems database
            // table belonging to the highlighted category.            
            Statement statement = _db.createStatement("DELETE FROM Expense WHERE fkExpenseType = ?"); 
            statement.prepare();
            statement.bind(1, entity.getId());            
            statement.execute();  
            statement.close();
        
        }
        catch(DatabaseException dbe)
        {
        	ExpenseUI.errorDialog(dbe.toString());
        }        
    }    
    
    public void deleteByAccount(Account entity)
    {
        try
        {
            // Delete all items in the DirectoryItems database
            // table belonging to the highlighted category.            
            Statement statement = _db.createStatement("DELETE FROM Expense WHERE fkAccount = ?"); 
            statement.prepare();
            statement.bind(1, entity.getId());            
            statement.execute();  
            statement.close();
        
        }
        catch(DatabaseException dbe)
        {
        	ExpenseUI.errorDialog(dbe.toString());
        }        
    }    
    
    /**
     * Retrieves all records in the Category database table and returns a hash table 
     * of Category objects.
     * @return A hash table of Category objects, one for each record in the Category table
     */
    public Vector getAll()
    {        
        Vector expenses = new Vector();
        try
        {
            // Read in all records from the Category table
            Statement statement = _db.createStatement("SELECT id, fkExpenseType, fkAccount, transType, amount, date FROM Expense"); 
            statement.prepare();
            Cursor cursor = statement.getCursor();              
            
            Row row;
            long id;
            ExpenseType expenseType;
        	double expenseAmount;
        	Account account;
        	String transType;
            Expense expense;
            Date date;
            ManageExpenseType typeManager = null;
            ManageAccount accountManager = null;
            // Iterate through the result set.  For each row, create a new
            // Category object and add it to the hash table. 
            while(cursor.next())
            {
            	row = cursor.getRow();               
                id = row.getInteger(0);
                
                if(typeManager == null){
                	typeManager = new ManageExpenseType(_db);
                }
                
                if(accountManager == null){
                	accountManager = new ManageAccount(_db);
                }
                
                expenseType = typeManager.get(row.getInteger(1));
                account = accountManager.get(row.getInteger(2));
                transType = row.getString(3);
                expenseAmount = row.getDouble(4);
                date = new Date(row.getLong(5));
                expense = new Expense(id, expenseType, expenseAmount, account, transType);
                expenses.addElement(expense);               
            }
            statement.close();
            cursor.close();
        }
        catch(DatabaseException dbe)
        {
        	ExpenseUI.errorDialog(dbe.toString());
        }
        catch(DataTypeException dte)
        {
        	ExpenseUI.errorDialog(dte.toString());
        }
        
        return expenses;
    }
    /**
     * 
     */
    public Expense getExpense(long id){
    	Expense expense = null;
    	try{
    		Statement statement = _db.createStatement("SELECT id, fkExpenseType, fkAccount, expenseAmount, date, transType FROM Expense where id = ?");
    		statement.prepare();
    		statement.bind(1, id);
    		Cursor cursor = statement.getCursor();
            ManageExpenseType typeManager = new ManageExpenseType(_db);
            ManageAccount accountManager = new ManageAccount(_db);
    		
    		if(cursor.next()){
    			Row row = cursor.getRow();
    			expense = new Expense();
    			expense.setId(row.getLong(0));
    			expense.setExpenseType(typeManager.get(row.getLong(1)));
    			expense.setAccount(accountManager.get(row.getLong(2)));
    			expense.setExpenseAmount(row.getDouble(3));
    			expense.setDate(new Date(row.getLong(4)));
    			expense.setTransType(row.getString(5));
    		}
    	} 
    	catch(DatabaseException dbe){
    		ExpenseUI.errorDialog(dbe.toString());
    	}
    	catch(DataTypeException dte){
    		ExpenseUI.errorDialog(dte.toString());
    	}
    	
    	return expense;
    }
}
